using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SouthernLand.Common.BOL.NhaDat;

namespace SouthernLand.Common.DAL.NhaDat
{
    public class QuanHuyenDAL : BaseDAL
    {
        #region ObjIDataReader

        /// <summary>
        /// Returns a new QuanHuyen instance filled with the DataReader's current record data 
        /// </summary>
        private QuanHuyen GetQuanHuyenFromReader(IDataReader dr)
        {
            var obj = new QuanHuyen();
            obj.QuanHuyenID = (dr["QuanHuyenID"] is DBNull) ? Int32.MinValue : (Int32) dr["QuanHuyenID"];
            obj.TinhThanhID = (dr["TinhThanhID"] is DBNull) ? Int32.MinValue : (Int32) dr["TinhThanhID"];
            obj.TenQuanHuyen = (dr["TenQuanHuyen"] is DBNull) ? String.Empty : (String) dr["TenQuanHuyen"];
            obj.TenQuanHuyen_en = (dr["TenQuanHuyen_en"] is DBNull) ? String.Empty : (String) dr["TenQuanHuyen_en"];
            return obj;
        }

        #endregion

        /// <summary>
        /// Returns a collection of QuanHuyen with the data read from the input DataReader
        /// </summary>
        private List<QuanHuyen> GetQuanHuyenCollectionFromReader(IDataReader dr)
        {
            var obj = new List<QuanHuyen>();
            while (dr.Read())
                obj.Add(GetQuanHuyenFromReader(dr));
            return obj;
        }

        public int Insert(QuanHuyen obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_QuanHuyen_Insert", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@QuanHuyenID", SqlDbType.Int).Value = obj.QuanHuyenID;
                myCommand.Parameters.Add("@TinhThanhID", SqlDbType.Int).Value = obj.TinhThanhID;
                myCommand.Parameters.Add("@TenQuanHuyen", SqlDbType.NVarChar).Value = obj.TenQuanHuyen;
                myCommand.Parameters.Add("@TenQuanHuyen_en", SqlDbType.NVarChar).Value = obj.TenQuanHuyen_en;
                myCommand.Parameters.Add("@ViTri", SqlDbType.Int).Value = obj.ViTri;
                return myCommand.ExecuteNonQuery();
            }
        }

        public bool Update(QuanHuyen obj)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_QuanHuyen_Update", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@QuanHuyenID", SqlDbType.Int).Value = obj.QuanHuyenID;
                myCommand.Parameters.Add("@TinhThanhID", SqlDbType.Int).Value = obj.TinhThanhID;
                myCommand.Parameters.Add("@TenQuanHuyen", SqlDbType.NVarChar).Value = obj.TenQuanHuyen;
                myCommand.Parameters.Add("@TenQuanHuyen_en", SqlDbType.NVarChar).Value = obj.TenQuanHuyen_en;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        public bool Delete(Int32 QuanHuyenID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_QuanHuyen_Delete", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@QuanHuyenID", SqlDbType.Int).Value = QuanHuyenID;

                int ret = myCommand.ExecuteNonQuery();
                return (ret == 1);
            }
        }

        /// <summary>
        /// Retrieves the w4u_QuanHuyen with the specified ID
        /// </summary>
        public QuanHuyen GetQuanHuyenByID(Int32 QuanHuyenID)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_QuanHuyen_GetQuanHuyenByID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@QuanHuyenID", SqlDbType.Int).Value = QuanHuyenID;

                SqlDataReader dr = myCommand.ExecuteReader();
                return (dr.Read() ? GetQuanHuyenFromReader(dr) : null);
            }
        }

        // Get QuanHuyenByTinhThanhID
        public List<QuanHuyen> GetQuanHuyenByTinhThanhID(Int32 TinhThanhID, int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_QuanHuyen_GetQuanHuyenByTinhThanhID", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@TinhThanhID", SqlDbType.Int).Value = TinhThanhID;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetQuanHuyenCollectionFromReader(dr);
            }
        }

        //Get All QuanHuyen
        public List<QuanHuyen> GetAllQuanHuyen(int PageIndex, int PageSize)
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_QuanHuyen_GetAllQuanHuyen", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
                SqlDataReader dr = myCommand.ExecuteReader();
                return GetQuanHuyenCollectionFromReader(dr);
            }
        }

        // Get Count All records of Table  QuanHuyen
        public int GetCountQuanHuyen()
        {
            using (SqlConnection myConn = GetConnectionString())
            {
                var myCommand = new SqlCommand("w4u_QuanHuyen_GetCountQuanHuyen", myConn);
                myCommand.CommandType = CommandType.StoredProcedure;
                return (int) myCommand.ExecuteScalar();
            }
        }
    }
}